from app.database.connector import Database
from app.database.dao import BaseDAO
from datetime import datetime

from app.utils.logger import logger


class ExhibitionDAO(BaseDAO):
    def __init__(self, db=None):
        super().__init__('exhibitions', db)
        self.datetime_fields = ['created_at', 'updated_at', 'event_date_start', 'event_date_end']  # 需要格式化的日期时间字段

    def _format(self, data):
        """格式化日期时间字段"""
        if not data:
            return data
        
        result = data.copy()
        for field in self.datetime_fields:
            if field in result and result[field]:
                if isinstance(result[field], datetime):
                    result[field] = result[field].strftime('%Y-%m-%d %H:%M:%S')
        return result

    def get_exhibition_by_code(self, code):
        """根据展会编号获取展会信息"""
        query = f"SELECT * FROM {self.table_name} WHERE exhibition_number = %s AND state = 0 LIMIT 1"
        result = self.db.execute_query(query, (code,))
        return result[0] if result else None


    def get_exhibition_list(self, page=1, page_size=10, conditions=None, order_by='id', order='desc'):
        """获取展会列表"""
        where_clauses = []
        params = []
        
        if conditions:
            for key, value in conditions.items():
                if isinstance(value, tuple) and key == '(chinese_name LIKE OR english_name LIKE or english_short_name LIKE)':
                    where_clauses.append("(chinese_name LIKE %s OR english_name LIKE %s OR english_short_name LIKE %s)")
                    params.extend([value[0], value[1],value[2]])
                elif isinstance(value, str) and key.endswith(' LIKE'):
                    field_name = key[:-5]
                    where_clauses.append(f"{field_name} LIKE %s")
                    params.append(value)
                else:
                    where_clauses.append(f"{key} = %s")
                    params.append(value)
        
        # 添加未删除条件
        where_clauses.append("state = 0")
        
        # 构建查询语句
        query = f"SELECT * FROM {self.table_name}"
        if where_clauses:
            query += " WHERE " + " AND ".join(where_clauses)
        
        # 添加排序
        query += f" ORDER BY {order_by} {order}"
        
        # 添加分页
        query += " LIMIT %s OFFSET %s"
        params.extend([page_size, (page - 1) * page_size])

        print(query,"查询的目前情况")
        
        results = self.db.execute_query(query, tuple(params))
        return [self._format(item) for item in results]

    def get_by_id(self, exhibition_id):
        result = super().find_by_id(exhibition_id)
        return self._format(result) if result else None

    def get_total_count(self, conditions=None):
        """获取符合条件的记录总数"""
        if conditions is None:
            conditions = {}
        conditions['state'] = 0  # 只查询未删除的记录
        
        where_clauses = []
        params = []
        
        for key, value in conditions.items():
            if isinstance(value, tuple) and key == '(chinese_name LIKE OR english_name LIKE)':
                where_clauses.append("(chinese_name LIKE %s OR english_name LIKE %s)")
                params.extend([value[0], value[1]])
            elif isinstance(value, str) and key.endswith(' LIKE'):
                field_name = key[:-5]
                where_clauses.append(f"{field_name} LIKE %s")
                params.append(value)
            else:
                where_clauses.append(f"{key} = %s")
                params.append(value)
                
        query = f"SELECT COUNT(*) as total FROM {self.table_name}"
        if where_clauses:
            query += " WHERE " + " AND ".join(where_clauses)
            
        result = self.db.execute_query(query, tuple(params))
        return result[0]['total'] if result else 0

    def update_exhibition(self, exhibition_id, data):
        """更新展会信息"""
        if not data:
            return None

        # 执行更新操作
        result = super().update(exhibition_id, data)

        return result if result else None

    def soft_delete_exhibition(self, exhibition_id):
        """软删除展会
        Args:
            exhibition_id: 展会ID
        Returns:
            bool: 删除是否成功
        """
        try:
            query = f"UPDATE {self.table_name} SET state = 1, updated_at = %s WHERE id = %s"
            params = (datetime.now(), exhibition_id)
            self.db.execute_update(query, params)
            return True
        except Exception as e:
            logger.error(f"软删除展会失败: {str(e)}")
            return False

    def get_exhibition_by_name(self, chinese_name):
        """根据展会中文名称查询展会信息"""
        try:
            query = f"SELECT * FROM {self.table_name} WHERE chinese_name = %s AND state = 0 LIMIT 1"
            result = self.db.execute_query(query, (chinese_name,))
            return result[0] if result else None
        except Exception as e:
            logger.error(f"查询展会名称失败: {str(e)}")
            return None

    def get_all_exhibitions(self):
        """获取所有展会数据"""
        sql = """
            SELECT 
                exhibition_number,
                chinese_name,
                english_name,
                english_short_name,
                logo,
                holding_time,
                continent,
                country,
                city,
                frequency,
                industry,
                organizer,
                organizer_en,
                contact_info,
                exhibition_hall,
                exhibition_hall_english,
                official_website,
                exhibit_scope,
                description,
                ufi_certified,
                exhibition_area,
                remarks,
                ai_checked_status,
                advice_check_description,
                error_check_description
            FROM exhibitions 
            WHERE state = 0
            ORDER BY industry, exhibition_number
        """
        results = self.db.execute_query(sql)
        return [self._format(item) for item in results]

    def get_exhibition_by_name_and_time(self, name, holding_time):
        """根据展会名称和举办时间查询展会"""
        sql = """
            SELECT * FROM exhibitions 
            WHERE chinese_name = %s AND holding_time = %s AND state = 0
            LIMIT 1
        """
        result = self.db.execute_query(sql, (name, holding_time))
        return result[0] if result else None
